﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using AG.Utilities;
using System.Data.SqlClient;

namespace SQLServerHealthCheck.SQLServer.AutoFixes
{
    public class DatabaseFixStateOffline : AutoFixBase
    {
        public DatabaseFixStateOffline()
        {
            IssueTypeId = 3004;
        }

        public override void Fix(List<Issue> issues)
        {
            try
            {

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                var Qry = from ii in issues
                          from iss in dc.Issues
                          from ser in dc.Services
                          from d in ser.Databases

                          where iss.IssueId == ii.IssueId
                          && iss.TargetId == d.DatabaseID
                         && iss.IssueTypeId == IssueTypeId
                          select new { Login = ser.Login, InstanceName = ser.InstanceName, cns = ser.GetConnectionString("Master"), databasename = d.DatabaseName, IssueId = iss.IssueId };
                ;

                foreach (var item in Qry)
                {
                    string connectionString;
                    connectionString = item.cns;
                    using (SqlConnection cn = new SqlConnection(connectionString))
                    {

                        string query = @"  ALTER DATABASE [" + item.databasename + "]  SET ONLINE ;";
                        SqlCommand cmd = new SqlCommand(query, cn);
                        cmd.Connection.Open();
                        cmd.ExecuteScalar();


                    }
                }

                foreach (var item in issues)
                {
                    Issue issue = dc.Issues.SingleOrDefault(i => i.IssueId == item.IssueId);
                    issue.Fixed = true;

                    dc.SubmitChanges();
                }
            }




            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }





    }
}
